#!/usr/bin/env python3
"""
Directory API Service for UmaInsight Frontend Integration
Provides optimized API endpoints for directory listing functionality
Optimized for http://localhost:3000/analysis/directory-list page performance
"""

import sqlite3
import sys
import os
import json
from datetime import datetime
from typing import List, Dict, Optional, Any
from dataclasses import dataclass, asdict
from contextlib import contextmanager

def get_database_path() -> str:
    """Get the path to the SQLite database"""
    # 从backend目录出发定位到data目录
    current_dir = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))  # backend/app/services -> backend/app -> backend
    backend_data_dir = os.path.join(current_dir, 'data')
    db_path = os.path.join(backend_data_dir, 'uma_insight.db')

    # 如果找不到，尝试从项目根目录查找
    if not os.path.exists(db_path):
        project_root = os.path.dirname(os.path.dirname(current_dir))  # backend -> project root
        db_path = os.path.join(project_root, 'backend', 'data', 'uma_insight.db')

    return db_path

@contextmanager
def get_db_connection():
    """Database connection context manager"""
    db_path = get_database_path()
    conn = sqlite3.connect(db_path)
    conn.row_factory = sqlite3.Row  # Enable dictionary-like row access
    try:
        yield conn
    finally:
        conn.close()

@dataclass
class DirectoryInfo:
    """Directory information for API response"""
    id: int
    name: str
    path: str
    metric_count: int
    created_at: str
    updated_at: str
    team_id: Optional[str] = None
    team_name: Optional[str] = None
    assignment_confidence: Optional[int] = None
    assignment_method: Optional[str] = None
    assigned_at: Optional[str] = None
    # Enhanced fields for accurate team distribution and analysis progress
    analyzed_count: Optional[int] = 0
    not_analyzed_count: Optional[int] = 0
    analysis_progress: Optional[int] = 0
    assigned_teams: Optional[List[str]] = None
    team_count: Optional[int] = 0

@dataclass
class MetricInfo:
    """Metric information for API response"""
    id: int
    name: str
    summary: Optional[str]
    team_name: Optional[str]
    directory_name: Optional[str]

@dataclass
class DirectoryListResponse:
    """Directory list API response structure"""
    success: bool
    data: List[DirectoryInfo]
    total_count: int
    execution_time_ms: float
    timestamp: str

@dataclass
class DirectoryMetricsResponse:
    """Directory metrics API response structure"""
    success: bool
    data: List[MetricInfo]
    directory_info: DirectoryInfo
    total_metrics: int
    page: int
    page_size: int
    execution_time_ms: float
    timestamp: str

class DirectoryAPIService:
    """Optimized directory API service for frontend consumption"""

    def __init__(self):
        self.db_path = get_database_path()

    def get_directories(self,
                       sort_by: str = 'metric_count',
                       order: str = 'DESC',
                       limit: Optional[int] = None,
                       offset: int = 0,
                       team_filter: Optional[str] = None) -> DirectoryListResponse:
        """
        Get directory list with optimized querying.

        This method has two query paths:
        1. No team_filter: A fast query that uses the denormalized metric_count from the directories table.
        2. With team_filter: A slower, more accurate query that calculates the metric count for the specific
           team by joining with the metrics table. This is necessary for correctness when filtering.
        """
        start_time = datetime.now()

        try:
            with get_db_connection() as conn:
                # Validate inputs
                valid_sort_fields = ['name', 'metric_count', 'created_at', 'updated_at']
                if sort_by not in valid_sort_fields:
                    sort_by = 'metric_count'

                order = order.upper() if order.upper() in ['ASC', 'DESC'] else 'DESC'

                query_params = []
                
                if team_filter:
                    if team_filter.lower() == "unassigned":
                        # FAST PATH FOR "UNASSIGNED" - uses the original logic based on directories.team_id
                        where_clause = "WHERE d.team_id IS NULL AND d.metric_count > 0"
                        query = f"""
                            SELECT
                                d.id, d.name, d.path, d.metric_count, d.created_at, d.updated_at,
                                d.team_id, d.assignment_confidence, d.assignment_method, d.assigned_at,
                                t.name as team_name
                            FROM directories d
                            LEFT JOIN teams t ON d.team_id = t.id
                            {where_clause}
                            ORDER BY d.{sort_by} {order}
                        """
                        count_query = f"SELECT COUNT(*) as total FROM directories d {where_clause}"
                    else:
                        # SLOWER, CORRECT PATH for specific team filtering
                        team_row = conn.execute("SELECT id FROM teams WHERE name = ?", (team_filter,)).fetchone()
                        if not team_row:
                            return DirectoryListResponse(
                                success=True, data=[], total_count=0, execution_time_ms=0,
                                timestamp=datetime.now().isoformat()
                            )
                        team_id = team_row['id']
                        
                        base_query = f"""
                            SELECT
                                d.id, d.name, d.path, COUNT(m.id) as metric_count, d.created_at, d.updated_at,
                                t.id as team_id, t.name as team_name, d.assignment_confidence, 
                                d.assignment_method, d.assigned_at
                            FROM directories d
                            JOIN metrics m ON d.id = m.directory_id
                            JOIN teams t ON m.team_id = t.id
                        """
                        count_query_base = "SELECT COUNT(DISTINCT d.id) FROM directories d JOIN metrics m ON d.id = m.directory_id JOIN teams t ON m.team_id = t.id"
                        
                        where_clause = " WHERE t.id = ?"
                        query_params.append(team_id)
                        
                        group_by_clause = " GROUP BY d.id, d.name, d.path, d.created_at, d.updated_at, t.id, t.name, d.assignment_confidence, d.assignment_method, d.assigned_at"
                        
                        query = base_query + where_clause + group_by_clause + f" ORDER BY {sort_by} {order}"
                        count_query = count_query_base + where_clause

                else:
                    # COMPREHENSIVE PATH - Calculate real-time data with analysis progress and team distribution
                    # New logic: NEEDS_COLLECTION/NOT_NEEDED = analyzed, everything else = not analyzed
                    # Exclude enum types from metric counts
                    query = f"""
                        SELECT
                            d.id, d.name, d.path, d.created_at, d.updated_at,
                            COUNT(m.id) as metric_count,
                            SUM(CASE WHEN m.analysis_status IN ('NEEDS_COLLECTION', 'NOT_NEEDED') THEN 1 ELSE 0 END) as analyzed_count,
                            SUM(CASE WHEN m.analysis_status NOT IN ('NEEDS_COLLECTION', 'NOT_NEEDED') OR m.analysis_status IS NULL OR m.analysis_status = '' THEN 1 ELSE 0 END) as not_analyzed_count,
                            ROUND(
                                (SUM(CASE WHEN m.analysis_status IN ('NEEDS_COLLECTION', 'NOT_NEEDED') THEN 1 ELSE 0 END) * 100.0 /
                                NULLIF(COUNT(m.id), 0)), 2
                            ) as analysis_progress,
                            COUNT(DISTINCT m.team_id) as team_count,
                            NULL as team_id, NULL as assignment_confidence, NULL as assignment_method, NULL as assigned_at,
                            NULL as team_name
                        FROM directories d
                        JOIN metrics m ON d.id = m.directory_id
                        WHERE m.type IN ('histogram', 'ukm_event')
                        GROUP BY d.id, d.name, d.path, d.created_at, d.updated_at
                        HAVING COUNT(m.id) > 0
                        ORDER BY {sort_by} {order}
                    """
                    count_query = "SELECT COUNT(DISTINCT d.id) as total FROM directories d JOIN metrics m ON d.id = m.directory_id WHERE m.type IN ('histogram', 'ukm_event')"

                total_count_row = conn.execute(count_query, query_params).fetchone()
                total_count = total_count_row[0] if total_count_row else 0

                if limit is not None:
                    query += f" LIMIT {limit} OFFSET {offset}"

                cursor = conn.execute(query, query_params)
                directories = []

                for row in cursor.fetchall():
                    # Get assigned teams for this directory
                    team_query = """
                        SELECT DISTINCT t.name as team_name
                        FROM teams t
                        JOIN metrics m ON t.id = m.team_id
                        WHERE m.directory_id = ?
                        ORDER BY t.name
                    """
                    team_cursor = conn.execute(team_query, (row['id'],))
                    assigned_teams = [team_row['team_name'] for team_row in team_cursor.fetchall()]

                    directory_info = DirectoryInfo(
                        id=row['id'],
                        name=row['name'],
                        path=row['path'],
                        metric_count=row['metric_count'],
                        created_at=row['created_at'],
                        updated_at=row['updated_at'],
                        team_id=row['team_id'],
                        team_name=row['team_name'],
                        assignment_confidence=row['assignment_confidence'],
                        assignment_method=row['assignment_method'],
                        assigned_at=row['assigned_at'],
                        # Enhanced fields
                        analyzed_count=row['analyzed_count'] or 0,
                        not_analyzed_count=row['not_analyzed_count'] or 0,
                        analysis_progress=row['analysis_progress'] or 0,
                        team_count=row['team_count'] or 0,
                        assigned_teams=assigned_teams
                    )
                    directories.append(directory_info)

                execution_time = (datetime.now() - start_time).total_seconds() * 1000

                return DirectoryListResponse(
                    success=True,
                    data=directories,
                    total_count=total_count,
                    execution_time_ms=round(execution_time, 2),
                    timestamp=datetime.now().isoformat()
                )

        except Exception as e:
            execution_time = (datetime.now() - start_time).total_seconds() * 1000
            print(f"Error in get_directories: {e}", file=sys.stderr)
            return DirectoryListResponse(
                success=False,
                data=[],
                total_count=0,
                execution_time_ms=round(execution_time, 2),
                timestamp=datetime.now().isoformat()
            )

    def get_directory_metrics(self,
                             directory_name: str,
                             page: int = 1,
                             page_size: int = 50,
                             sort_by: str = 'name',
                             order: str = 'ASC') -> DirectoryMetricsResponse:
        """
        Get metrics in a specific directory with pagination

        Args:
            directory_name: Name of the directory
            page: Page number (1-based)
            page_size: Number of items per page
            sort_by: Sort field for metrics
            order: Sort order
        """
        start_time = datetime.now()

        try:
            with get_db_connection() as conn:
                # Get directory info (search by both name and path)
                dir_query = """
                    SELECT
                        d.id, d.name, d.path, d.metric_count, d.created_at, d.updated_at,
                        d.team_id, d.assignment_confidence, d.assignment_method, d.assigned_at,
                        t.name as team_name
                    FROM directories d
                    LEFT JOIN teams t ON d.team_id = t.id
                    WHERE (d.name = ? OR d.path = ?) AND d.metric_count > 0
                """
                dir_row = conn.execute(dir_query, (directory_name, directory_name)).fetchone()

                if not dir_row:
                    execution_time = (datetime.now() - start_time).total_seconds() * 1000
                    return DirectoryMetricsResponse(
                        success=False,
                        data=[],
                        directory_info=None,
                        total_metrics=0,
                        page=page,
                        page_size=page_size,
                        execution_time_ms=round(execution_time, 2),
                        timestamp=datetime.now().isoformat()
                    )

                directory_info = DirectoryInfo(
                    id=dir_row['id'],
                    name=dir_row['name'],
                    path=dir_row['path'],
                    metric_count=dir_row['metric_count'],
                    created_at=dir_row['created_at'],
                    updated_at=dir_row['updated_at'],
                    team_id=dir_row['team_id'],
                    team_name=dir_row['team_name'],
                    assignment_confidence=dir_row['assignment_confidence'],
                    assignment_method=dir_row['assignment_method'],
                    assigned_at=dir_row['assigned_at']
                )

                # Get total metrics count
                count_query = """
                    SELECT COUNT(*) as total
                    FROM metrics m
                    JOIN metric_directories md ON m.id = md.metric_id
                    JOIN directories d ON md.directory_id = d.id
                    WHERE d.name = ?
                """
                total_metrics = conn.execute(count_query, (directory_name,)).fetchone()['total']

                # Build metrics query with pagination
                offset = (page - 1) * page_size
                metrics_query = f"""
                    SELECT
                        m.id,
                        m.name,
                        m.summary,
                        mta.team_name
                    FROM metrics m
                    JOIN metric_directories md ON m.id = md.metric_id
                    JOIN directories d ON md.directory_id = d.id
                    LEFT JOIN metric_team_assignments mta ON m.id = mta.metric_id
                    WHERE d.name = ?
                    ORDER BY m.{sort_by} {order}
                    LIMIT {page_size} OFFSET {offset}
                """

                cursor = conn.execute(metrics_query, (directory_name,))
                metrics = [
                    MetricInfo(
                        id=row['id'],
                        name=row['name'],
                        summary=row['summary'],
                        team_name=row['team_name'],
                        directory_name=directory_name
                    )
                    for row in cursor.fetchall()
                ]

                execution_time = (datetime.now() - start_time).total_seconds() * 1000

                return DirectoryMetricsResponse(
                    success=True,
                    data=metrics,
                    directory_info=directory_info,
                    total_metrics=total_metrics,
                    page=page,
                    page_size=page_size,
                    execution_time_ms=round(execution_time, 2),
                    timestamp=datetime.now().isoformat()
                )

        except Exception as e:
            execution_time = (datetime.now() - start_time).total_seconds() * 1000
            return DirectoryMetricsResponse(
                success=False,
                data=[],
                directory_info=None,
                total_metrics=0,
                page=page,
                page_size=page_size,
                execution_time_ms=round(execution_time, 2),
                timestamp=datetime.now().isoformat()
            )

    def search_directories(self, search_term: str, limit: int = 20) -> DirectoryListResponse:
        """
        Search directories by name
        """
        start_time = datetime.now()

        try:
            with get_db_connection() as conn:
                query = """
                    SELECT id, name, path, metric_count, created_at, updated_at
                    FROM directories
                    WHERE (name LIKE ? OR path LIKE ?) AND metric_count > 0
                    ORDER BY metric_count DESC, name
                    LIMIT ?
                """

                cursor = conn.execute(query, (f'%{search_term}%', f'%{search_term}%', limit))
                directories = [
                    DirectoryInfo(
                        id=row['id'],
                        name=row['name'],
                        path=row['path'],
                        metric_count=row['metric_count'],
                        created_at=row['created_at'],
                        updated_at=row['updated_at']
                    )
                    for row in cursor.fetchall()
                ]

                execution_time = (datetime.now() - start_time).total_seconds() * 1000

                return DirectoryListResponse(
                    success=True,
                    data=directories,
                    total_count=len(directories),
                    execution_time_ms=round(execution_time, 2),
                    timestamp=datetime.now().isoformat()
                )

        except Exception as e:
            execution_time = (datetime.now() - start_time).total_seconds() * 1000
            return DirectoryListResponse(
                success=False,
                data=[],
                total_count=0,
                execution_time_ms=round(execution_time, 2),
                timestamp=datetime.now().isoformat()
            )

    def get_directory_stats(self) -> Dict[str, Any]:
        """Get directory statistics summary"""
        try:
            with get_db_connection() as conn:
                queries = {
                    'total_directories': "SELECT COUNT(*) as count FROM directories WHERE metric_count > 0",
                    'total_metrics': "SELECT SUM(metric_count) as count FROM directories WHERE metric_count > 0",
                    'largest_directory': """
                        SELECT name, metric_count
                        FROM directories
                        WHERE metric_count > 0
                        ORDER BY metric_count DESC
                        LIMIT 1
                    """,
                    'avg_metrics': "SELECT AVG(metric_count) as count FROM directories WHERE metric_count > 0"
                }

                results = {}
                for key, query in queries.items():
                    row = conn.execute(query).fetchone()
                    if key == 'largest_directory':
                        results[key] = {
                            'name': row['name'],
                            'count': row['count']
                        } if row else None
                    else:
                        results[key] = row['count'] if row['count'] else 0

                return {
                    'success': True,
                    'data': results
                }

        except Exception as e:
            return {
                'success': False,
                'error': str(e)
            }

def demo_api_service():
    """Demonstrate the API service functionality"""
    print("=== Directory API Service 演示 ===")

    service = DirectoryAPIService()

    print("\n1. 获取Directory列表 (按metric数量排序):")
    response = service.get_directories(limit=10)
    print(f"   ✅ 成功: {response.success}")
    print(f"   ⏱️  执行时间: {response.execution_time_ms}ms")
    print(f"   📊 总数: {response.total_count}")
    print(f"   📁 Top Directories:")
    for i, directory in enumerate(response.data[:5], 1):
        print(f"      {i}. {directory.name} ({directory.metric_count} metrics)")

    print("\n2. 获取'Android' Directory中的Metrics:")
    response = service.get_directory_metrics('Android', page=1, page_size=5)
    print(f"   ✅ 成功: {response.success}")
    print(f"   ⏱️  执行时间: {response.execution_time_ms}ms")
    if response.success:
        print(f"   📁 Directory: {response.directory_info.name}")
        print(f"   📊 总Metrics: {response.total_metrics}")
        print(f"   📋 Metrics (前5个):")
        for i, metric in enumerate(response.data, 1):
            team_info = f" [{metric.team_name}]" if metric.team_name else ""
            print(f"      {i}. {metric.name}{team_info}")

    print("\n3. 搜索包含'ash'的Directory:")
    response = service.search_directories('ash', limit=5)
    print(f"   ✅ 成功: {response.success}")
    print(f"   ⏱️  执行时间: {response.execution_time_ms}ms")
    print(f"   🔍 搜索结果:")
    for directory in response.data:
        print(f"      - {directory.name} ({directory.metric_count} metrics)")

    print("\n4. Directory统计信息:")
    stats = service.get_directory_stats()
    if stats['success']:
        data = stats['data']
        print(f"   📊 总Directory数量: {data['total_directories']}")
        print(f"   📈 总Metrics数量: {data['total_metrics']}")
        print(f"   📏 平均Metrics/Directory: {data['avg_metrics']:.1f}")
        if data['largest_directory']:
            print(f"   🏆 最大Directory: {data['largest_directory']['name']} ({data['largest_directory']['count']} metrics)")

if __name__ == "__main__":
    demo_api_service()